-- Add columns to dimension

IF COL_LENGTH('[DW].[dOffers]', 'Period description') IS NULL
BEGIN
ALTER TABLE [DW].[dOffers]
ADD 
	[Period description] nvarchar(30) NULL,
	[Valid from] date NULL,
	[Valid to] date NULL
END
-- ALTER Stored Procedure

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[dimMergedOffers] 
@Item [dbo].[OffersType] READONLY

AS 
BEGIN

WITH tMaxRowID
AS
(SELECT
		sOFF.CompanyPrefix
	   ,sOFF.[No_]
	   ,sOFF.[Offer Type]
	   ,MAX(sOFF.[bigint_timestamp]) AS [RowID]
	   ,'Offer' AS [SourceTable]
	FROM [stg$Offer] sOFF
	GROUP BY sOFF.[CompanyPrefix]
			,sOFF.[No_]
			,sOFF.[Offer Type]
	UNION
	SELECT
		sPDI.[CompanyPrefix]
	   ,sPDI.[No_]
	   ,sPDI.[Offer Type]
	   ,MAX(sPDI.[bigint_timestamp]) AS [RowID]
	   ,'Periodic Disc' AS [SourceTable]
	FROM [stg$Periodic Discount] sPDI
	GROUP BY sPDI.[CompanyPrefix]
			,sPDI.[No_]
			,sPDI.[Offer Type]
	UNION
	SELECT 
		 sTDE.[CompanyPrefix]
		,sTDE.[Offer No_]
		,sTDE.[Offer Type]
		,MAX(sTDE.[bigint_timestamp]) AS [RowID]
		,'Transaction' AS [SourceTable]
	FROM [stg$Trans_ Discount Entry] sTDE
	WHERE sTDE.[Offer No_]-- In @@MissingOffersAndDiscounts
	IN (SELECT DISTINCT
			sTDE.[Offer No_]
		FROM [stg$Trans_ Discount Entry] sTDE
		WHERE sTDE.[Offer No_] NOT IN (SELECT sOFF.[No_]
									FROM [stg$Offer] sOFF
									UNION
									SELECT sPDI.[No_]
									FROM [stg$Periodic Discount] sPDI))
	GROUP BY sTDE.[CompanyPrefix]
			,sTDE.[Offer No_]
			,sTDE.[Offer Type]
	),

tOffer
AS
(SELECT
		sOFF.[CompanyPrefix]
	   ,sOFF.[No_]
	   ,'Offer' AS [Table Source]
	   ,sOFF.[Description]
	   ,sOFF.[Status]
	   ,CASE sOFF.[Status]
			WHEN 0 THEN 'Disabled'
			WHEN 1 THEN 'Enabled'
		END AS [StatusDescription]
	   ,sOFF.[Offer Type]
	   ,CASE sOFF.[Offer Type]
			WHEN 0 THEN 'Promotion'
			WHEN 1 THEN 'Deal'
			WHEN 2 THEN 'Multibuy'
			WHEN 3 THEN 'Mix&Match'
			WHEN 4 THEN 'Disc. Offer'
			WHEN 5 THEN 'Total Discount'
			WHEN 6 THEN 'Tender Type'
			WHEN 7 THEN 'Item Point'
			WHEN 8 THEN 'Line Discount'
			WHEN 9 THEN 'Customer'
			WHEN 10 THEN 'Infocode'
		END AS [OfferTypeDescription]
	   ,sOFF.[Price Group]
	   ,sOFF.[Validation Period ID] AS [ValidationPeriodID]
	   ,sOFF.[bigint_timestamp] AS [RowID]
	FROM [stg$Offer] sOFF

	UNION

	SELECT
		sPDI.[CompanyPrefix]
	   ,sPDI.[No_]
	   ,'Periodic Discount' AS [Table Source]
	   ,sPDI.[Description]
	   ,sPDI.[Status]
	   ,CASE sPDI.[Status]
			WHEN 0 THEN 'Disabled'
			WHEN 1 THEN 'Enabled'
		END AS [StatusDescription]
	   ,sPDI.[Offer Type]
	   ,CASE sPDI.[Offer Type]
			WHEN 0 THEN 'Promotion'
			WHEN 1 THEN 'Deal'
			WHEN 2 THEN 'Multibuy'
			WHEN 3 THEN 'Mix&Match'
			WHEN 4 THEN 'Disc. Offer'
			WHEN 5 THEN 'Total Discount'
			WHEN 6 THEN 'Tender Type'
			WHEN 7 THEN 'Item Point'
			WHEN 8 THEN 'Line Discount'
			WHEN 9 THEN 'Customer'
			WHEN 10 THEN 'Infocode'
		END AS [OfferTypeDescription]
	   ,sPDI.[Price Group]
	   ,sPDI.[Validation Period ID]
	   ,sPDI.[bigint_timestamp] AS [RowID]
	FROM [stg$Periodic Discount] sPDI

	UNION

	SELECT
		sTDE.[CompanyPrefix]
	   ,sTDE.[Offer No_]
	   ,'Transaction' AS [Table Source]
	   ,'Missing from Discounts' AS [Description]
	   ,-1 AS [Status]
	   ,'N/A' AS [StatusDescription]
	   ,sTDE.[Offer Type]
	   ,CASE sTDE.[Offer Type]
			WHEN 0 THEN 'Promotion'
			WHEN 1 THEN 'Deal'
			WHEN 2 THEN 'Multibuy'
			WHEN 3 THEN 'Mix&Match'
			WHEN 4 THEN 'Disc. Offer'
			WHEN 5 THEN 'Total Discount'
			WHEN 6 THEN 'Tender Type'
			WHEN 7 THEN 'Item Point'
			WHEN 8 THEN 'Line Discount'
			WHEN 9 THEN 'Customer'
			WHEN 10 THEN 'Infocode'
			WHEN 11 THEN 'Member Point'
			WHEN 12 THEN 'Coupon'
			WHEN 13 THEN 'Total'
			WHEN 14 THEN 'Line'
			ELSE 'No Offer type'
		END AS [Offer Type Description]
	   ,'N/A' AS [Price Group]
	   ,'' AS [ValidationPeriodID]
	   ,sTDE.[bigint_timestamp] RowID
	FROM [stg$Trans_ Discount Entry] sTDE
	WHERE sTDE.[Offer No_]-- In @@MissingOffers
	IN (SELECT DISTINCT
			sTDE.[Offer No_]
		FROM [stg$Trans_ Discount Entry] sTDE
		WHERE sTDE.[Offer No_] NOT IN (SELECT sOFF.[No_]
									FROM [stg$Offer] sOFF
									UNION
									SELECT sPDI.[No_]
									FROM [stg$Periodic Discount] sPDI)))

,MaxRowIDValPer AS
(SELECT MAX([bigint_timestamp]) AS [bigint_timestamp]
,[CompanyPrefix], [ID]
from [stg$Validation Period]
GROUP BY
[CompanyPrefix], [ID]
),
tValidationPeriod AS 
(
SELECT sVaP.[CompanyPrefix]
      ,sVaP.[bigint_timestamp]
      ,sVaP.[ID]
      ,COALESCE(sVaP.[Description], 'No Time Limit') AS [Description]
      ,CASE WHEN sVaP.[Starting Date] = '1753-01-01' THEN NULL ELSE sVaP.[Starting Date] END AS [Starting Date]
      ,CASE WHEN sVaP.[Ending Date]  = '1753-01-01' THEN NULL ELSE  sVaP.[Ending Date] END AS [Ending Date]   
  FROM [stg$Validation Period] sVaP
  left join MaxRowIDValPer MRI
  ON 1=1
  AND sVaP.[bigint_timestamp] = MRI.[bigint_timestamp]
  )								
	

MERGE [DW].[dOffers] AS TARGET USING 
(SELECT
		COALESCE(dCOM.[SK_Company],-1) AS [Company]
	   ,tOFF.[No_]
	   ,tOFF.[Table Source]
	   ,tOFF.[Description]
	   ,tOFF.[Status]
	   ,tOFF.[StatusDescription]
	   ,tOFF.[Offer Type]
	   ,tOFF.[OfferTypeDescription]
	   ,tOFF.[Price Group]
	   ,tOFF.[ValidationPeriodID]
	   ,tVaP.[Description] AS [Period description]
      ,tVaP.[Starting Date]
      ,tVaP.[Ending Date]
	   ,GETUTCDATE() AS [Batchdate]
	   ,tMRI.[RowID]
	FROM [tOffer] tOFF
	RIGHT JOIN [tMaxRowID] tMRI
		ON tOFF.[CompanyPrefix] = tMRI.[CompanyPrefix]
		AND tOFF.[No_] = tMRI.[No_]
		AND tOFF.[Offer Type] = tMRI.[Offer Type]
		AND tOFF.[RowID] = tMRI.[RowID]
	LEFT JOIN [DW].[dCompany] dCOM
		ON tOFF.[CompanyPrefix] = dCOM.[CompanyPrefix]
	LEFT JOIN tValidationPeriod tVaP
		ON tOFF.[CompanyPrefix] = tVaP.[CompanyPrefix]
		AND tOFF.[ValidationPeriodID] = tVaP.[ID]
) AS Source

ON TARGET.[No_] = Source.[No_]
	AND TARGET.[Offer Type] = Source.[Offer Type]
	AND TARGET.[Company] = Source.[Company]

WHEN MATCHED
	THEN UPDATE
		SET [Company] = Source.[Company]
		   ,[No_] = Source.[No_]
		   ,[Table Source] = Source.[Table Source]
		   ,[Description] = Source.[Description]
		   ,[Status] = Source.[Status]
		   ,[StatusDescription] = Source.[StatusDescription]
		   ,[Offer Type] = Source.[Offer Type]
		   ,[OfferTypeDescription] = Source.[OfferTypeDescription]
		   ,[Price Group] = Source.[Price Group]
		   ,[ValidationPeriodID] = Source.[ValidationPeriodID]
		   ,[Period description] = Source.[Period description]
			,[Valid from] = Source.[Starting Date]
			,[Valid to] = Source.[Ending Date]
		   ,[Batchdate] = Source.[Batchdate]

WHEN NOT MATCHED BY TARGET
	THEN INSERT ([Company], [No_], [Table Source], [Description], [Status], [StatusDescription], [Offer Type], [OfferTypeDescription], [Price Group], [ValidationPeriodID],[Period description],[Valid from],[Valid to], [Batchdate])
			VALUES (Source.[Company], Source.[No_], Source.[Table Source], Source.[Description], Source.[Status], Source.[StatusDescription], Source.[Offer Type], Source.[OfferTypeDescription], Source.[Price Group], Source.[ValidationPeriodID],Source.[Period description] ,Source.[Starting Date] ,Source.[Ending Date], Source.[Batchdate])

WHEN NOT MATCHED BY SOURCE
	AND TARGET.[SK_Offer] <> -1
	THEN DELETE;
;
END

SELECT
	'RowCount' = @@rowcount